package core.persistance;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {
	
	private String connectionUrl = "jdbc:sqlite:data.db";
	
	public Database() throws ClassNotFoundException, SQLException {

		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection(connectionUrl);
		Statement stat = conn.createStatement();
		stat.executeUpdate("CREATE TABLE IF NOT EXISTS Profiles ("
				+ "id INTEGER PRIMARY KEY, " + "name TEXT, "
				+ "username TEXT, " + "password TEXT, " + "hostname TEXT, "
				+ "port INTEGER);");
		conn.close();
	}

	public int addProfile(Profile p) throws SQLException {
		Connection conn = DriverManager.getConnection(connectionUrl);
		ResultSet rs = null;
		int generatedKey;
		PreparedStatement ps = conn
				.prepareStatement("INSERT INTO Profiles(name, username, password, hostname, port) "
						+ "VALUES (?, ?, ?, ?, ?);");
		ps.setString(1, p.getName());
		ps.setString(2, p.getUsername());
		ps.setString(4, p.getHostname());
		ps.setInt(5, p.getPort());
		ps.executeUpdate();
		rs = ps.getGeneratedKeys();
		if (rs.next())
			generatedKey = rs.getInt(1);
		else
			generatedKey = -1;
		conn.close();
		return generatedKey;
	}
	
	public void updateProfile(Profile p) throws SQLException {
		Connection conn = DriverManager.getConnection(connectionUrl);
		PreparedStatement ps = conn.prepareStatement("UPDATE Profiles "
				+ "SET name=?, username=?, password=?, hostname=?, port=? "
				+ "WHERE id=?;");
		ps.setString(1, p.getName());
		ps.setString(2, p.getUsername());
		ps.setString(3, p.getPassword());
		ps.setString(4, p.getHostname());
		ps.setInt(5, p.getPort());
		ps.setInt(6, p.getId());
		ps.executeUpdate();
		conn.close();
	}
	
	public void deleteProfile(int id) throws SQLException {
		Connection conn = DriverManager.getConnection(connectionUrl);
		PreparedStatement ps = conn.prepareStatement("DELETE FROM Profiles WHERE id=?;");
		ps.setInt(1, id);
		ps.executeUpdate();
		conn.close();
	}
	
	public Profile[] getAllProfiles() throws SQLException {
		Connection conn = DriverManager.getConnection(connectionUrl);
		ResultSet rs;
		int elementsNo = 0;
		Statement stat = conn.createStatement();
		rs = stat.executeQuery("SELECT count(*) FROM Profiles");
		if (rs.next());
			elementsNo = rs.getInt(1);
		
		Profile[] profiles = new Profile[elementsNo];
		rs = stat.executeQuery("SELECT * FROM Profiles;");
		int i = 0;
		while (rs.next()) {
			profiles[i] = new Profile();
			profiles[i].setId(rs.getInt("id"));
			profiles[i].setName(rs.getString("name"));
			profiles[i].setUsername(rs.getString("username"));
			profiles[i].setPassword(rs.getString("password"));
			profiles[i].setHostname(rs.getString("hostname"));
			profiles[i].setPort(rs.getInt("port"));
			i++;
		}
		return profiles;
	}
	
	public Profile getProfile(int id) throws SQLException {
		Connection conn = DriverManager.getConnection(connectionUrl);
		ResultSet rs;
		PreparedStatement ps = conn.prepareStatement("SELECT * FROM Profiles WHERE id=?;");
		ps.setInt(1, id);
		
		Profile p = new Profile();
		rs = ps.executeQuery();
		if (rs.next()) {
			p.setId(rs.getInt("id"));
			p.setName(rs.getString("name"));
			p.setUsername(rs.getString("username"));
			p.setPassword(rs.getString("password"));
			p.setHostname(rs.getString("hostname"));
			p.setPort(rs.getInt("port"));
		}
		return p;
	}
}
